<%@page import="java.util.*"%>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Registration Confirmation</title>
</head>

<body>


            <%-- Import the java.sql package --%>
            <%@ page import="java.sql.*"%>
            <%-- -------- Open Connection Code -------- --%>
            <%
            
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            
            
            try {
                // Registering Postgresql JDBC driver with the DriverManager
                Class.forName("org.postgresql.Driver");

                // Open a connection to the database using DriverManager
                conn = DriverManager.getConnection(
                    "jdbc:postgresql://localhost/phase2db?" +
                    "user=postgres&password=password");
            %>
            
            
            <%-- -------- INSERT Code -------- --%>
            <%              	

                    // Begin transaction
                    conn.setAutoCommit(false);
            		

                    // Create the prepared statement and use it to
                    // INSERT student values INTO the students table.
                    
                    /*
                    pstmt = conn
                    .prepareStatement("INSERT INTO accounts (username, password, email) VALUES (?, ?, ?)");

                    pstmt.setString(1, request.getParameter("username"));
                    pstmt.setString(2, request.getParameter("password"));
                    pstmt.setString(3, request.getParameter("email"));

                    int rowCount = pstmt.executeUpdate();    
                    
               
                    
                    // Commit transaction
                    conn.commit();
                    conn.setAutoCommit(true);
                    
                    */
            %>



            <%-- -------- SELECT Statement Code -------- --%>
            <%
                // Create the statement
                Statement statement = conn.createStatement();

                // Use the created statement to SELECT
                rs = statement.executeQuery("SELECT * FROM users");
                
                
                
            	boolean needToInsert = true;
                
                while (rs.next())  {
                	
                	// if the account is already in the database, no need to insert into database.
                	if (request.getParameter("user_name").equals(rs.getString("user_name")))  {
                		needToInsert = false;
                	}
                	
                	// else the account is new. need to insert into database
                	else  {
                		// DO NOTHING
                	}
                	
                }
                
                
                
                if (needToInsert == false)  {
                	out.println ("You are already registered, you may proceed to submit an application.");
                }
                else  {
                	pstmt = conn
                    .prepareStatement("INSERT INTO users (user_name, password, email, status) VALUES (?, md5(?), ?, ?)");

                    pstmt.setString(1, request.getParameter("user_name"));
                    pstmt.setString(2, request.getParameter("password"));
                    pstmt.setString(3, request.getParameter("email"));
                    pstmt.setString(4, "notSubmitted");

                    int rowCount = pstmt.executeUpdate();  
                    
                    
                    
                    rs = statement.executeQuery("SELECT * FROM users");
                    
                    int idNumber = 0;
                    while (rs.next()){
                    	idNumber = rs.getInt("id");
                    }
                 
             		
                    pstmt = conn.prepareStatement("INSERT INTO user_roles (user_ref, user_name, role) VALUES (?,?,?)");
                    pstmt.setInt(1, idNumber);  //ID OF WHAT WAS JUST INSERTED
                    
                    pstmt.setString(2, request.getParameter("user_name"));  //USERNAME OF WHAT WAS JUST INSERTED
                    pstmt.setString(3, "student");  // ROLE
                    rowCount = pstmt.executeUpdate(); 
               
                    // Commit transaction
                    conn.commit();
                    conn.setAutoCommit(true);
                 
                    out.println("Registration successful!  Account information is now stored in the database.");
                }
                %>
                

            
                              

			

            <%-- -------- Close Connection Code -------- --%>
            <%
                // Close the ResultSet
                rs.close();

                // Close the Statement
                statement.close();

                // Close the Connection
                conn.close();
            } catch (SQLException e) {

                // Wrap the SQL exception in a runtime exception to propagate
                // it upwards
                throw new RuntimeException(e);
            }
            finally {
                // Release resources in a finally block in reverse-order of
                // their creation

                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) { } // Ignore
                    rs = null;
                }
                if (pstmt != null) {
                    try {
                        pstmt.close();
                    } catch (SQLException e) { } // Ignore
                    pstmt = null;
                }
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) { } // Ignore
                    conn = null;
                }
            }
            %>
            
            <p>
            You are registered as a student.
            <p>
            <a href="/CSE135_Project_struts"> Click to login and start your application. </a>
            


</body>

</html>